/*
 * Copyright 2011 J. Auer, J. Andert, F.-J. Jendrusch, U. Celebi
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package de.fuberlin.replication.notepad.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * This Class opens a connection to the SQLite DB.
 */
public class NotesDatabase {
	public String DATABASE_NAME = "notepad.db";

	/**
	 * Name of SQLite table which contains the notes
	 */
	public static final String NOTES_TABLE_NAME = "notes";

	private Connection connection = null;

	/**
	 * Return the <code>Connection</code> object for the DB connection. The caller has to make sure that the connection
	 * was previously opened, otherwise she will get a null as a return value.
	 * 
	 * @return Connection to the SQLite DB, null if not connection was opened previously.
	 */
	public Connection getConnection() {
		return connection;
	}

	/**
	 * Open the DB connection.
	 * 
	 * @throws SQLException If couldn't establish connection to DB
	 * @throws ClassNotFoundException If JDBC driver is not found
	 */
	public void open() throws SQLException, ClassNotFoundException {
		// Try closing connection before opening a new one
		close();

		Class.forName("org.sqlite.JDBC");
		connection =
				DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
		create();
		// drop();
	}

	/**
	 * Close the DB connection.
	 * 
	 * @throws SQLException If couldn't close connection
	 */
	public void close() throws SQLException {
		if (connection != null) {
			connection.close();
			connection = null;
		}
	}

	/**
	 * Create notes table.
	 * 
	 * @throws SQLException If couldn't create table
	 */
	private void create() throws SQLException {
		Connection conn = getConnection();

		PreparedStatement prep = conn.prepareStatement("CREATE TABLE IF NOT EXISTS notes "
				+ "(title TEXT PRIMARY KEY, note TEXT);");

		prep.executeUpdate();

		prep.close();
	}

	/**
	 * Drop notes table.
	 * 
	 * @throws SQLException If couldn't drop table
	 */
	public void drop() throws SQLException {
		Connection conn = getConnection();

		PreparedStatement prep = conn
				.prepareStatement("DROP TABLE IF EXISTS notes;");

		prep.executeUpdate();

		prep.close();
	}
}